﻿using Client.Library.Helpers;
using iTextSharp.text;
using MongoDB.Driver;
//using iTextSharp.text.pdf;
using SQLite.Data;
using SQLServer.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data.OleDb;

namespace Client.Library
{
    public static class DataExport
    {
        public static void ExportSalesReportXml()
        {
            SupermarketEntities ctx = new SupermarketEntities();

            using (ctx)
            {
                XElement sales = new XElement("sales");

                foreach (var vendor in ctx.Vendors.Include("Products"))
                {
                    var salesByVendor =
                        from product in vendor.Products
                        join sale in ctx.Sales
                            on product.ID equals sale.ProductID
                        select new { OrderDate = sale.OrderDate, Quantity = sale.Quantity,
                            ProductID = sale.ProductID, BasePrice = product.BasePrice };

                    XElement saleEl = new XElement("sale");
                    saleEl.Add(new XAttribute("vendor", vendor.Name));

                    foreach (var product in vendor.Products)
                    {
                        foreach (var sale in product.Sales)
                        {
                            // Calculate total sum
                            var productSalesByVendor = salesByVendor.
                                Where(s => s.OrderDate == sale.OrderDate && s.ProductID == product.ID);
                            decimal totalSum = 0;

                            foreach (var pr in productSalesByVendor)
                            {
                                totalSum += pr.Quantity * pr.BasePrice;
                            }

                            XElement summary = new XElement("summary");
                            summary.Add(new XAttribute("date", sale.OrderDate.ToShortDateString()));
                            summary.Add(new XAttribute("total-sum", totalSum));
                            saleEl.Add(summary);
                        }
                    }

                    sales.Add(saleEl);
                }

                XmlDocument doc = new XmlDocument();
                doc.LoadXml(sales.ToString());
                doc.Save("../../sales.xml");
                Console.WriteLine(sales);
            }
        }

        public static void ExportProductReportJSON()
        {
            List<ProductReport> reports = new List<ProductReport>();
            SupermarketEntities ctx = new SupermarketEntities();
            var connectionString = "mongodb://localhost";
            var client = new MongoClient(connectionString);
            var server = client.GetServer();
            var database = server.GetDatabase("product-reports");
            var collection = database.GetCollection<ProductReport>("product-reports");

            if (collection.Exists())
            {
                collection.RemoveAll();
            }

            using (ctx)
            {
                foreach (var vendor in ctx.Vendors.Include("Products"))
                {
                    foreach (var product in vendor.Products)
                    {
                        long soldQuantity = 0;
                        if (product.Sales.Count == 0)
                        {
                            continue;
                        }

                        foreach (var sale in product.Sales)
                        {
                            soldQuantity += sale.Quantity;
                        }

                        decimal totalIncome = soldQuantity * product.BasePrice;

                        var prodReport = new ProductReport
                        {
                            ProductId = product.ID,
                            ProductName = product.ProductName,
                            VendorName = vendor.Name,
                            SoldQuantity = soldQuantity,
                            TotalIncome = totalIncome
                        };

                        collection.Insert(prodReport);
                        reports.Add(prodReport);
                    }
                }
            }

            ExportToJSON(reports);
        }

        private static void ExportToJSON(List<ProductReport> reports)
        {
            string rootDIr = "../../Product-Reports/";

            foreach (var report in reports)
            {
                using (StreamWriter writer = new StreamWriter(rootDIr + report.ProductId + ".json", false))
                {
                    writer.Write(report.ToString());
                }
            }
        }

        public static void GeneratePdf()
        {
            StringBuilder sb = new StringBuilder();

            using (SupermarketEntities se = new SupermarketEntities())
            {
                sb.Append("<h1>Aggregated Sales Report</h1><br />");
                

                var dates = se.Sales.Select(x => x.OrderDate).Distinct();

                decimal grandTotalSum = 0.0M;
                sb.Append("<table>");

                foreach (var date in dates)
                {
                    var salesByDate = se.Sales.Where(x => x.OrderDate == date);
                    decimal totalSum = 0.0M;

                    sb.Append("<tr><td>");
                    sb.AppendFormat("<p>Date: {0}</p><br /><br />", se.Products.ToList()[0].Sales.Where(d => d.OrderDate != null).Select(da => da.OrderDate).FirstOrDefault().ToString());
                    sb.Append("<table border=\"1\">");
                    sb.Append("<tr>");
                    sb.Append("<th><b>Product</b></th>");
                    sb.Append("<th><b>Quantity</b></th>");
                    sb.Append("<th><b>Unit Price</b></th>");
                    sb.Append("<th><b>Location</b></th>");
                    sb.Append("<th><b>Sum</b></th>");
                    sb.Append("</tr>");

                    foreach (var product in se.Products)
                    {
                        foreach (var supermarket in se.Supermarkets)
                        {
                            var productSales = salesByDate.Where(x => x.ProductID == product.ID && x.SupermarketID == supermarket.ID);
                            int quatity = 0;

                            foreach (var sale in productSales)
                            {
                                quatity += sale.Quantity;
                            }

                            if (quatity == 0)
                            {
                                continue;
                            }
                            sb.Append("<tr>");
                            sb.AppendFormat("<td>{0}</td>", product.ProductName);
                            sb.AppendFormat("<td>{0} {1}</td>", quatity, product.Measure.Name);
                            sb.AppendFormat("<td>{0:F2}</td>", product.BasePrice);
                            sb.AppendFormat("<td>{0}</td>", supermarket.Location);
                            sb.AppendFormat("<td>{0:F2}</td>", quatity * product.BasePrice);
                            sb.Append("</tr>");

                            totalSum += (quatity * product.BasePrice);
                            
                        }
                    }
                    sb.AppendFormat("<tr><td colspan=\"4\">Total Sum for {0}:</td> <td>{1:F2}</td></tr>", date, totalSum);
                    sb.Append("</table>");
                    sb.Append("<br /><br />");

                    grandTotalSum += totalSum;

                    sb.Append("</td></tr>");
                }
                sb.AppendFormat("<tr><td>Grand Total Sum: {0:F2}<td></tr>",  grandTotalSum);
                sb.Append("</table>");
                /*foreach (var product in se.Products.Include("Sales"))
                {
                    foreach (var sale in product.Sales)
                    {
                        
                        sb.Append("<tr>");
                        sb.AppendFormat("<td>{0}</td>", product.ProductName);
                        sb.AppendFormat("<td>{0} {1}</td>", sale.Quantity, product.Measure.Name);
                        sb.AppendFormat("<td>{0:F2}</td>", product.BasePrice);
                        sb.AppendFormat("<td>{0}</td>", sale.Supermarket.Location);
                        sb.AppendFormat("<td>{0:F2}</td>", sale.Quantity * product.BasePrice);
                        sb.Append("</tr>");

                        totalSum += (sale.Quantity * product.BasePrice);
                    }
                
                }*/

                

                PDFBuilder.HtmlToPdfBuilder builder = new PDFBuilder.HtmlToPdfBuilder(PageSize.A4);
                //builder.ImportStylesheet(@"../../" + AppDomain.CurrentDomain.DynamicDirectory + "style.css"); 
                //builder.AddStyle("p", "background-color: #0f0;"); 

                PDFBuilder.HtmlPdfPage page = builder.AddPage();
                page.AppendHtml(sb.ToString());
                byte[] file = builder.RenderPdf();

                string folder = @"../../" + AppDomain.CurrentDomain.DynamicDirectory + "PdfResult\\";
                string fileName = DateTime.Now.ToString("yyyy-MM-dd") + "-" + Guid.NewGuid() + ".pdf";

                if (Helpers.Helpers.DirectoryExist(folder))
                {
                    if (!File.Exists(folder + fileName))
                        File.WriteAllBytes(folder + fileName, file);
                }
            }
        }

        public static void ExportProductReportToXLS(List<ProductsTotalReport> records, string location)
        {
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = @"..\..\report.xlsx";
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES;");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                using (OleDbCommand command = connection.CreateCommand())
                {
                    connection.Open();
                    command.CommandType = CommandType.Text;
                    command.CommandText = "CREATE TABLE products(Vendor STRING,Incomes DECIMAL,Expenses DECIMAL, Taxes DECIMAL, FinancialResult DECIMAL)";

                    command.ExecuteNonQuery();
                    
                    foreach (var record in records)
                    {
                        using (OleDbCommand insertCommand = connection.CreateCommand())
                        {
                            insertCommand.CommandType = CommandType.Text;
                            insertCommand.CommandText = "INSERT INTO  products(Vendor, Incomes, Expenses, Taxes, FinancialResult) " +
                                "VALUES(@vendor, @incomes, @expenses, @taxes, @financialResult)";
                            insertCommand.Parameters.AddWithValue("@vendor", record.VendorName);
                            insertCommand.Parameters.AddWithValue("@incomes", record.Incomes);
                            insertCommand.Parameters.AddWithValue("@expenses", record.Expenses);
                            insertCommand.Parameters.AddWithValue("@taxes", record.Taxes);
                            insertCommand.Parameters.AddWithValue("@financialResult", record.FinancialResult);

                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
}
